﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
/*
	Just added another helper method for column mod.
*/
namespace SqlCeDriver
{
	public class SqlTableSchema
	{
		String tableName = "";
		List<DbView.FieldInfo> fields;

		public SqlTableSchema()
		{
			this.fields = new List<DbView.FieldInfo>();
		}

		public int Count { get { return this.fields.Count; } }

		public DbView.FieldInfo this[int i]
		{
			get { return this.fields[i]; }
		}

		public DbView.FieldInfo this[String name]
		{
			get
			{
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.Name.ToUpper().CompareTo(name.ToUpper()) == 0)
						return fi;
				return null;
			}
		}

		public DbView.FieldInfo IdentityField
		{
			get
			{
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsAuto)
						return fi;
				return null;
			}
		}

		public DbView.FieldInfo[] PKeyFields
		{
			get
			{
				int keyCount = 0;
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsPKey)
						++keyCount;
				DbView.FieldInfo[] pkeyList = new DbView.FieldInfo[keyCount];
				keyCount = 0;
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsPKey)
						pkeyList[keyCount++] = fi;
				return pkeyList;
			}
		}

		// very tied to the schema info
		public void Populate(DataTable schemaTable,
							String nameCol, String typeCol, String lenCol, String decimalCol, String pkeyCol, String autoCol,
							String fkeyCol, String indexCol, String calcCol, String defCol, String definitionCol, String nullCol)
		{
			this.tableName = schemaTable.TableName;

			// find 
			foreach (DataRow dr in schemaTable.Rows)
			{
				String name = this.DataFromCell(dr, nameCol);
				String sqlType = this.DataFromCell(dr, typeCol);
				String leng = this.DataFromCell(dr, lenCol);
				String dp = this.DataFromCell(dr, decimalCol);
				String pk = this.DataFromCell(dr, pkeyCol);
				String fk = this.DataFromCell(dr, fkeyCol);
				String auto = this.DataFromCell(dr, autoCol);
				String index = this.DataFromCell(dr, indexCol);
				String calc = this.DataFromCell(dr, calcCol);
				String nulls = this.DataFromCell(dr, nullCol); nulls = (nulls.Length == 0) ? "Yes" : nulls; // Special. For null column blank means Yes
				String definition = this.DataFromCell(dr, definitionCol);
				//
				int len = (leng.ToUpper() == "MAX") ? -1 :
						  (leng.Length == 0) ? 0 : // 2000 driver
						   Convert.ToInt32(leng);

				DbView.FieldInfo fi = new DbView.FieldInfo(name, sqlType, len);
				fi.IsAuto = this.TfFromData(auto); // should resolve auto inc info to Y if present
				this.ParseAutoData(fi, auto);
				fi.IsPKey = this.TfFromData(pk);
				fi.IsFKey = this.TfFromData(fk);
				fi.IsIndexed = this.TfFromData(index);
				if (sqlType.ToUpper() == "NCHAR" || sqlType.ToUpper() == "NVARCHAR")
					fi.IsUnicode = true;
				fi.IsCalc = this.TfFromData(calc);
				fi.AllowsNulls = this.TfFromData(nulls);
				fi.HasDefault = (definition.Length > 0);
				fi.ConstraintDefinition = definition;
				fi.Dp = (dp.Length == 0) ? 0 : Convert.ToInt32(dp);
				this.fields.Add(fi);
			}
		}

		String DataFromCell(DataRow dr, String columnName)
		{
			if (!dr.Table.Columns.Contains(columnName)) return "";
			return dr[columnName].ToString();
		}

		bool TfFromData(String data)
		{
			if (data.Length == 0) return false;  // empty means no
			char c = data[0];
			if (c == 'Y' || c == 'y' || c == 'T' || c == 't') return true;
			if (c == '1') return true;
			if (c == '0') return true;
			// Unless N or No then true
			if ((c == 'N' || c == 'n') && data.Length <= 2) return false;
			// handle other cases as they arise
			return true;
		}
		// check for (m,m)
		void ParseAutoData(DbView.FieldInfo fi, String autoData)
		{
			SimpleRegex sr = new SimpleRegex(@"\(\s*(\d+)\s*,\s*(\d+)\s*\)");
			if (sr.MatchText(autoData))
			{
				fi.AutoSeed = Convert.ToInt32(sr[1]);
				fi.AutoInc = Convert.ToInt32(sr[2]);
			}
		}
	}

	public class SqlScriptingHelper
	{
		String sourceTable;
		readonly SqlTableSchema schema;

		public SqlTableSchema Schema { get { return this.schema; } }

		public SqlScriptingHelper(String sourceTable, SqlTableSchema schema)
		{
			this.sourceTable = sourceTable;
			this.schema = schema;
		}

		public String ScriptKeyCompare() { return this.ScriptKeyCompare(this.sourceTable); }
		public String ScriptKeyCompare(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					if (script.Length > 0)
						script += " and";
					script += String.Format(" [{1}].[{0}]=[{2}].[{0}]", fi.Name, targetTable, this.sourceTable);
				}
			}
			return script;
		}

		public String ScriptKeyList() { return this.ScriptKeyList(this.sourceTable); }
		public String ScriptKeyList(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					if (script.Length > 0)
						script += ", ";
					script += String.Format(" [{1}].[{0}]", fi.Name, targetTable);
				}
			}
			return script;
		}

		public String ScriptUpdate() { return this.ScriptUpdate(this.sourceTable); }
		public String ScriptUpdate(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsPKey && !fi.IsAuto && !fi.IsCalc)
				{
					if (script.Length > 0)
						script += ",\n";
					script += String.Format(" [{1}].[{0}]=[{2}].[{0}]", fi.Name, targetTable, this.sourceTable);
				}
			}
			return script;
		}

		public String ScriptCheckSumCompare(bool forEquality) { return this.ScriptCheckSumCompare(this.sourceTable, forEquality); }
		public String ScriptCheckSumCompare(String targetTable, bool forEquality)
		{
			String eq = (forEquality) ? "=" : "!=";
			String[] src = new String[this.schema.Count];
			String[] tgt = new String[this.schema.Count];
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				src[idx] = String.Format("[{0}].[{1}]", this.sourceTable, fi.Name);
				tgt[idx] = String.Format("[{0}].[{1}]", targetTable, fi.Name);
			}
			return String.Format("CHECKSUM({0}){2}\nCHECKSUM({1})", String.Join(",", src), String.Join(",", tgt), eq);
		}

		public String ScriptAllFields()
		{
			String[] src = new String[this.schema.Count];
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				src[idx] = String.Format("[{0}]", fi.Name);
			}
			return String.Join(",", src);
		}

		public String ScriptAllButCalcFields()
		{
			List<String> src = new List<string>();
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsCalc)
					src.Add(String.Format("[{0}]", fi.Name));
			}
			return String.Join(",", src.ToArray());
		}

		public bool TablesCompare(SqlTableSchema compareSchema)
		{
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (compareSchema[fi.Name] == null)
					return false;
			}
			return true;
		}

		public String ScriptRowsetRefresh(DbView.ConnectionInfo sourceConnectionInfo)
		{
			String script = "/*  Script to refresh a table (0) */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			this.sourceTable = String.Format("#{0}", this.sourceTable);

			script += String.Format(@"
if object_id('tempdb..{0}') is not null drop table {0}
select dt.* into {0} from OPENROWSET('SQLNCLI','{1}';'{2}';'{3}', {4}.dbo.{5}) AS dt
            ", this.sourceTable, sourceConnectionInfo.Server, sourceConnectionInfo.UID, sourceConnectionInfo.PWD, sourceConnectionInfo.Database, this.sourceTable);

			script += String.Format(@"
delete from [{0}] where not exists
       (select * from [{1}] where {2})
            ", this.sourceTable, this.sourceTable, this.ScriptKeyCompare());
			/*
update BANK set 
	BANK_NAME = BANK_20081017_103911.BANK_NAME
 from BANK_20081017_103911
where BANK.BANK_ID = BANK_20081017_103911.BANK_ID
 and CHECKSUM(BANK.BANK_ID) <> CHECKSUM(BANK_20081017_103911.BANK_ID)
			*/
			script += String.Format(@"
update [{0}] set {2}
  from [{1}]
 where {3} 
   and {4}
            ", this.sourceTable, this.sourceTable, this.ScriptUpdate(),
					   this.ScriptKeyCompare(),
					   this.ScriptCheckSumCompare(false));

			DbView.FieldInfo idField = this.schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
                set IDENTITY_INSERT [{0}] ON; 
                ", this.sourceTable);
			}
			/*
insert into  BANK (list)
select list
 from BANK2
where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
insert into [{0}] ({2})
select {2} 
  from [{1}] 
 where not exists (select * from [{0}] where {3}) 
            ", this.sourceTable, this.sourceTable, this.ScriptAllButCalcFields(), this.ScriptKeyCompare());

			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
set IDENTITY_INSERT [{0}] OFF; 
                ", this.sourceTable);

				script += String.Format(@"
declare @reseed int
select @reseed = max({2}) from [{1}]
DBCC CHECKIDENT ([{0}], RESEED, @reseed)
                ", this.sourceTable, this.sourceTable, idField.Name);
			}
			return script;
		}

		public String ScriptTableRefresh()
		{
			String script = "/*  Script to refresh a table (0) */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
            delete from [{0}] where not exists
                   (select * from [{1}] where {2})
            ", this.sourceTable, this.sourceTable, this.ScriptKeyCompare());

			/*
update BANK set 
	BANK_NAME = BANK_20081017_103911.BANK_NAME
 from BANK_20081017_103911
where BANK.BANK_ID = BANK_20081017_103911.BANK_ID
 and CHECKSUM(BANK.BANK_ID) <> CHECKSUM(BANK_20081017_103911.BANK_ID)
			*/
			script += String.Format(@"
            update [{0}] set {2}
              from [{1}]
             where {3} 
               and {4}
            ", this.sourceTable, this.sourceTable, this.ScriptUpdate(),
					   this.ScriptKeyCompare(),
					   this.ScriptCheckSumCompare(false));

			DbView.FieldInfo idField = this.schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
                set IDENTITY_INSERT [{0}] ON; 
                ", this.sourceTable);
			}
			/*
insert into  BANK (list)
select list
 from BANK2
where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
            insert into [{0}] ({2})
            select {2} 
              from [{1}] 
             where not exists (select * from [{0}] where {3}) 
            ", this.sourceTable, this.sourceTable, this.ScriptAllButCalcFields(), this.ScriptKeyCompare());

			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
                set IDENTITY_INSERT [{0}] OFF; 
                ", this.sourceTable);

				script += String.Format(@"
                declare @reseed int
                select @reseed = max({2}) from [{1}]
                DBCC CHECKIDENT ([{0}], RESEED, @reseed)
                ", this.sourceTable, this.sourceTable, idField.Name);
			}
			return script;
		}
	}
}
